/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Common.Utils;
import Model.Dto.Account;
import Model.Dto.jaxb.AccountBalances.AccountBalances;
import Model.Dto.jaxb.AccountBalances.AccountType;
import Model.Dto.jaxb.Accounts.Accounts;
import Model.Dto.jaxb.Transasctions.TransactionDetailType;
import Model.Dto.jaxb.Transasctions.TransactionType;
import Model.Dto.jaxb.Transasctions.Transactions;
import com.sun.org.apache.xerces.internal.jaxp.datatype.XMLGregorianCalendarImpl;
import java.io.ByteArrayInputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.xml.datatype.XMLGregorianCalendar;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

/**
 *
 * @author Quang Nhat
 */
public class TransactionModel {

    static class XMLTransactionHandler extends DefaultHandler {

        private Transactions res = new Transactions();
        private TransactionType tran = null;
        private TransactionDetailType detail = null;
        private StringBuilder explanation = new StringBuilder();
        private String currTag;

        public Transactions getResult() {
            return res;
        }

        @Override
        public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
            if (qName.equals("Transaction")) {
                tran = new TransactionType();
                String date = attributes.getValue("Date");
                String[] parts = date.split("-");
                XMLGregorianCalendar d = new XMLGregorianCalendarImpl();
                d.setYear(Integer.parseInt(parts[0]));
                d.setMonth(Integer.parseInt(parts[1]));
                d.setDay(Integer.parseInt(parts[2]));
                tran.setDate(d);
                tran.setId(new BigInteger(attributes.getValue("Id")));
            } else if (qName.equals("Detail")) {
                detail = new TransactionDetailType();
                detail.setId(new BigInteger(attributes.getValue("Id")));
                detail.setAccountId(new BigInteger(attributes.getValue("AccountId")));
                detail.setAmount(new BigDecimal(attributes.getValue("Amount")));
                detail.setIsDebit(attributes.getValue("IsDebit").equalsIgnoreCase("true"));
                tran.getDetail().add(detail);
            } else if (qName.equals("Explanation")) {
                explanation.setLength(0);
            }
            currTag = qName;
        }

        @Override
        public void endElement(String uri, String localName, String qName) throws SAXException {
            if (qName.equals("Transaction")) {
                res.getTransaction().add(tran);
            } else if (qName.equals("Explanation")) {
                tran.setExplanation(explanation.toString());
            }
        }

        @Override
        public void characters(char[] ch, int start, int length) throws SAXException {
            if (currTag.equals("Explanation")) {
                explanation.append(ch, start, length);
            }
        }
    }

    public static Transactions parseXML(String xmlContent) {

        try {
            SAXParserFactory spf = SAXParserFactory.newInstance();
            SAXParser parser = spf.newSAXParser();
            XMLTransactionHandler handler = new XMLTransactionHandler();
            parser.parse(new ByteArrayInputStream(xmlContent.getBytes("UTF-8")), handler);
            return handler.getResult();
        } catch (Exception ex) {
            Utils.ReportException(ex);
        }
        return null;
    }

    private static int addTransactionDetail(int id, TransactionDetailType detail) {
        Connection con = null;
        ResultSet rs = null;
        ResultSet keys = null;
        PreparedStatement stm = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("INSERT INTO TransactionDetails(TransactionId, AccountId, Amount) "
                    + "VALUES(?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            stm.setInt(1, id);
            stm.setInt(2, detail.getAccountId().intValue());
            double amount = detail.getAmount().doubleValue();
            if (!detail.isIsDebit()) {
                amount = -amount;
            }
            stm.setDouble(3, amount);
            if (stm.executeUpdate() > 0) {
                keys = stm.getGeneratedKeys();
                keys.next();

                return keys.getInt(1);
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }

            if (keys != null) {
                try {
                    keys.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return 0;
    }

    public static int addTransaction(TransactionType tran) {
        Connection con = null;
        ResultSet rs = null;
        ResultSet keys = null;
        PreparedStatement stm = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("INSERT INTO Transactions "
                    + "VALUES(?, ?)", Statement.RETURN_GENERATED_KEYS);
            XMLGregorianCalendar d = tran.getDate();
            stm.setDate(1, new Date(d.getYear() - 1900, d.getMonth() - 1, d.getDay()));
            stm.setNString(2, tran.getExplanation());
            if (stm.executeUpdate() > 0) {
                keys = stm.getGeneratedKeys();
                keys.next();
                int id = keys.getInt(1);
                tran.setId(BigInteger.valueOf(id));
                for (TransactionDetailType detail : tran.getDetail()) {
                    detail.setId(BigInteger.valueOf(addTransactionDetail(id, detail)));
                }
                return id;
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }

            if (keys != null) {
                try {
                    keys.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return 0;
    }

    public static boolean updateTransaction(TransactionType tran) {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement stm = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("UPDATE Transactions "
                    + " SET Date = ?, Explaination = ? "
                    + " WHERE Id = ?");
            XMLGregorianCalendar d = tran.getDate();
            stm.setDate(1, new Date(d.getYear() - 1900, d.getMonth() - 1, d.getDay()));
            stm.setNString(2, tran.getExplanation());
            stm.setInt(3, tran.getId().intValue());
            if (stm.executeUpdate() > 0) {
                int id = tran.getId().intValue();
                stm = con.prepareStatement("DELETE TransactionDetails "
                        + "WHERE TransactionId = ?");
                stm.setInt(1, id);
                stm.executeUpdate();
                for (TransactionDetailType detail : tran.getDetail()) {
                    detail.setId(BigInteger.valueOf(addTransactionDetail(id, detail)));
                }
                return true;
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return false;
    }

    public static boolean deleteTransaction(int id) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("DELETE TransactionDetails "
                    + "WHERE TransactionId = ?");
            stm.setInt(1, id);
            stm.executeUpdate();
            stm = con.prepareStatement("DELETE Transactions "
                    + "WHERE Id = ?");
            stm.setInt(1, id);
            if (stm.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return false;
    }

    public static TransactionType getTransactionById(int id) {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement stm = null;
        TransactionType result = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("SELECT * FROM Transactions "
                    + "WHERE Id = ?");
            stm.setInt(1, id);
            rs = stm.executeQuery();
            if (rs.next()) {
                result = new TransactionType();
                result.setId(BigInteger.valueOf(id));
                result.setExplanation(rs.getNString("Explaination"));
                Date d = rs.getDate("Date");
                XMLGregorianCalendar date = new XMLGregorianCalendarImpl();
                date.setYear(d.getYear() + 1900);
                date.setMonth(d.getMonth() + 1);
                date.setDay(d.getDate() + 2);
                result.setDate(date);

                stm = con.prepareStatement("SELECT * FROM TransactionDetails "
                        + "WHERE TransactionId = ?");
                stm.setInt(1, id);
                rs = stm.executeQuery();
                while (rs.next()) {
                    TransactionDetailType detail = new TransactionDetailType();
                    detail.setId(BigInteger.valueOf(rs.getInt("Id")));
                    detail.setAccountId(BigInteger.valueOf(rs.getInt("AccountId")));
                    double amount = rs.getDouble("Amount");
                    detail.setAmount(BigDecimal.valueOf(Math.abs(amount)));
                    detail.setIsDebit(amount > 0);
                    result.getDetail().add(detail);
                }
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return result;
    }

    public static Transactions searchTransaction(Date minDate, Date maxDate, String text) {
        Transactions result = new Transactions();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement stm = null;
        List<Account> accList = AccountModel.getAllAccount();
        Map<Integer, String> accMap = new HashMap<Integer, String>(accList.size());
        for (Account acc : accList) {
            accMap.put(acc.getAccountNumber(), acc.getAccountName());
        }
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("SELECT * FROM Transactions "
                    + "WHERE Explaination LIKE ? "
                    + "AND (1=? OR Date >= ?) "
                    + "AND (1=? OR Date <= ?) "
                    + "ORDER BY Date DESC");
            stm.setNString(1, "%" + text + "%");
            if (minDate == null) {
                stm.setInt(2, 1);
                stm.setDate(3, new Date(0));
            } else {
                stm.setInt(2, 2);
                stm.setDate(3, minDate);
            }
            if (maxDate == null) {
                stm.setInt(4, 1);
                stm.setDate(5, new Date(0));
            } else {
                stm.setInt(4, 2);
                stm.setDate(5, maxDate);
            }
            rs = stm.executeQuery();
            while (rs.next()) {
                result.getTransaction().add(getTransactionById(rs.getInt("Id")));
            }
            for (TransactionType tran : result.getTransaction()) {
                for (TransactionDetailType d : tran.getDetail()) {
                    int id = d.getAccountId().intValue();
                    if (accMap.containsKey(id)) {
                        d.setAccountName(accMap.get(id));
                    }
                }
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return result;
    }

    public static Map<Account, Double> getAccountBalances(Date date) {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement stm = null;
        List<Account> accList = AccountModel.getAllAccount();
        Map<Account, Double> result = new HashMap<Account, Double>(accList.size());
        Map<Integer, Account> accMap = new HashMap<Integer, Account>(accList.size());
        for (Account acc : accList) {
            result.put(acc, 0.0);
            accMap.put(acc.getAccountNumber(), acc);
        }
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("SELECT AccountId, SUM(Amount) AS Amount "
                    + " FROM TransactionDetails d JOIN Transactions t ON d.TransactionId = t.Id "
                    + " WHERE t.Date <= ? "
                    + " GROUP BY AccountId ");
            stm.setDate(1, date);
            rs = stm.executeQuery();
            while (rs.next()) {
                result.put(accMap.get(rs.getInt("AccountId")), rs.getDouble("Amount"));
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return result;
    }
    public static AccountBalances getJaxbAccountBalances(Date date) {
        AccountBalances res = new AccountBalances();
        Map<Account, Double> accs = getAccountBalances(date);
        for (Account acc : accs.keySet()) {
            AccountType bal = new AccountType();
            bal.setAccountNumber(BigInteger.valueOf(acc.getAccountNumber()));
            bal.setAccountName(acc.getAccountName());
            bal.setActive(acc.isActive());
            double amount = accs.get(acc);
            bal.setBalance(BigDecimal.valueOf(Math.abs(amount)));
            bal.setIsDebit(amount >= 0);
            res.getAccount().add(bal);
        }
        return res;
    }
}
